package com.oa.ylms.apps.dao.archives.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.oa.ylms.apps.constants.IDEDStates;
import com.oa.ylms.apps.dao.archives.IStaffInfoDao;
import com.oa.ylms.apps.entity.archives.ArchiveInfoValue;
import com.oa.ylms.apps.entity.archives.StaffInfoValue;
import com.oa.ylms.apps.entity.organization.SectionInfoValue;

public class StaffInfoDaoImpl extends HibernateDaoSupport implements
		IStaffInfoDao {
	private List<StaffInfoValue> list;
	private int numbers;
	@Override
	public int addStaffInfo(StaffInfoValue staffInfo) throws Exception {
		if (staffInfo != null) {
			this.getHibernateTemplate().save(staffInfo);
			return staffInfo.getStaffId();
		}
		return 0;
	}

	@Override
	public boolean editStaffInfo(StaffInfoValue staffInfo) throws Exception {
		if (staffInfo != null) {
			try {
				this.getHibernateTemplate().update(staffInfo);
			} catch (Exception e) {
				return false;
			}
			return true;
		}
		return false;
	}

	@Override
	public StaffInfoValue queryByStaffId(StaffInfoValue staffInfo)
			throws Exception {
		if (staffInfo != null) {
			return (StaffInfoValue) this.getHibernateTemplate().get(
					StaffInfoValue.class, staffInfo.getStaffId());
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<StaffInfoValue> queryStaffInfo(final StaffInfoValue staffInfo, final ArchiveInfoValue archiveInfo, final int rows, final int pageNo) throws Exception {
		String hqlStr = "SELECT siv FROM StaffInfoValue siv, ArchiveInfoValue aiv WHERE 1=1 AND siv.archiveInfo.archiveId = aiv.archiveId";
		if (staffInfo.getStaffNo() != null) {
			hqlStr += " AND siv.staffNo = ?";
		}
		if (archiveInfo.getPostInfo() != null) {
			hqlStr += " AND aiv.postInfo = ? ";
		}
		if (archiveInfo.getStaffName() != null) {
			hqlStr += " AND aiv.staffName=?";
		}
		if (staffInfo.getStates() != null) {
			hqlStr += " AND siv.states = ?";
		}
		hqlStr += " AND siv.states <> ?  ORDER BY siv.staffNo DESC";
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = -1;
				if (staffInfo.getStaffNo() != null) {
					query.setString(++index, staffInfo.getStaffNo());
				}
				if (archiveInfo.getPostInfo() != null) {
					query.setEntity(++index, archiveInfo.getPostInfo());
				}
				if (archiveInfo.getStaffName() != null) {
					query.setString(++index, archiveInfo.getStaffName());
				}
				if (staffInfo.getStates() != null) {
					query.setInteger(++index, staffInfo.getStates());
				}
				query.setInteger(++index, IDEDStates.DELETE);
				query.setFirstResult(rows * (pageNo - 1));
				query.setMaxResults(rows);
				list = query.list();
				return list;
			}
		});
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public int countTPage(final StaffInfoValue staffInfo, final ArchiveInfoValue archiveInfo, final int rows)
			throws Exception {
		int pageNumbers = 0;
		String hqlStr = "SELECT siv FROM StaffInfoValue siv, ArchiveInfoValue aiv WHERE 1=1 AND siv.archiveInfo.archiveId = aiv.archiveId";
		if (staffInfo.getStaffNo() != null) {
			hqlStr += " AND siv.staffNo = ?";
		}
		if (archiveInfo.getPostInfo() != null) {
			hqlStr += " AND aiv.postInfo = ? ";
		}
		if (archiveInfo.getStaffName() != null) {
			hqlStr += " AND aiv.staffName=?";
		}
		if (staffInfo.getStates() != null) {
			hqlStr += " AND siv.states = ?";
		}
		hqlStr += " AND siv.states <> ?  ORDER BY siv.staffNo DESC";
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = -1;
				if (staffInfo.getStaffNo() != null) {
					query.setString(++index, staffInfo.getStaffNo());
				}
				if (archiveInfo.getPostInfo() != null) {
					query.setEntity(++index, archiveInfo.getPostInfo());
				}
				if (archiveInfo.getStaffName() != null) {
					query.setString(++index, archiveInfo.getStaffName());
				}
				if (staffInfo.getStates() != null) {
					query.setInteger(++index, staffInfo.getStates());
				}
				query.setInteger(++index, IDEDStates.DELETE);
				list = query.list();
				return list;
			}
		});
		numbers = list.size();
		if (numbers <= rows) {
			pageNumbers = 1;
		} else {
			if (numbers % rows == 0) {
				pageNumbers = numbers / rows;
			} else {
				pageNumbers = numbers / rows + 1;
			}
		}
		return pageNumbers;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<StaffInfoValue> queryBySectionId(SectionInfoValue sectionInfo)
			throws Exception {
		String hql = "SELECT siv FROM StaffInfoValue siv, PostInfoValue piv,ArchiveInfoValue aiv WHERE siv.archiveInfo.archiveId=aiv.archiveId AND aiv.postInfo.postId = piv.postId AND piv.sectionInfo = ? AND siv.states=1";
		return this.getHibernateTemplate().find(hql, sectionInfo);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<StaffInfoValue> verifyStaffInfo(final StaffInfoValue staffInfo) throws Exception {
		String hqlStr = "FROM StaffInfoValue siv WHERE 1=1";
		if(staffInfo.getStaffNo() != null){
			hqlStr += " AND siv.staffNo = ?";
		}
		if(staffInfo.getStaffPwd() != null){
			hqlStr += " AND siv.staffPwd = ?";
		}
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = -1;
				if(staffInfo.getStaffNo() != null){
					query.setString(++index, staffInfo.getStaffNo());
				}
				if(staffInfo.getStaffPwd() != null){
					query.setString(++index, staffInfo.getStaffPwd());
				}
				list = query.list();
				return list;
			}
		});
		return list;
	}
	
	@Override
	public int getTotalNumber(){
		return numbers;
	}
	
	
	//
	public List<StaffInfoValue> getList() {
		return list;
	}

	public void setList(List<StaffInfoValue> list) {
		this.list = list;
	}

	public int getNumbers() {
		return numbers;
	}

	public void setNumbers(int numbers) {
		this.numbers = numbers;
	}

}